package webx.utils;

import stdx.Utils;
import webx.LogFile;

import java.util.List;
import java.io.Closeable;
import java.util.ArrayList;
import java.sql.SQLException;
import java.lang.reflect.Field;

public class DBConnect implements Closeable {
	protected dbx.DBConnect conn;
	protected int loglevel = LogFile.TIP;
	protected Status status = new Status();

	public static class Status{
		private int rowcnt;
		private String sqlcmd;
		private Object[] args;
		private Exception error;

		public String toString(){
			String res;

			if (error == null){
				res = String.format("execute sqlcmd[%s] success[%d]", sqlcmd, rowcnt);
			}
			else{
				res = String.format("execute sqlcmd[%s] failed[%s]", sqlcmd, error.getMessage());
			}

			if (args == null || args.length < 1) return res;

			res += " with param";

			for (Object item : args) res += "[" + String.valueOf(item) + "]";

			return res;
		}
		public int getRowCount(){
			return rowcnt;
		}
		public String getCommand(){
			return sqlcmd;
		}
		public Object[] getParamlist(){
			return args;
		}
		public int update(int rowcnt, String sqlcmd, Object[] args, Exception error) {
			this.args = args;
			this.error = error;
			this.rowcnt = rowcnt;
			this.sqlcmd = sqlcmd;

			return rowcnt;
		}
	}

	protected void updateStatus(String sqlcmd, Object[] args, Exception error){
		updateStatus(Utils.SYSERR, sqlcmd, args, error);
	}
	protected int updateStatus(int rowcnt, String sqlcmd, Object[] args, Exception error){
		status.update(rowcnt, sqlcmd, args, error);

		if (error == null){
			if (loglevel <= LogFile.TIP) LogFile.Trace(LogFile.TIP, status.toString());
		}
		else{
			if (loglevel <= LogFile.ERR) LogFile.Trace(LogFile.ERR, status.toString());
		}

		return rowcnt;
	}

	public void close(){
		Utils.Close(conn);
		conn = null;
	}
	public Status getLastStatus(){
		return status;
	}
	public void setLogLevel(int level){
		loglevel = level;
	}
	public DBConnect(dbx.DBConnect conn){
		this.conn = conn;
	}
	public void commit() throws SQLException{
		conn.commit();
	}
	public void rollback() throws SQLException{
		conn.rollback();
	}
	public boolean getAutoCommit() throws SQLException{
		return conn.getAutoCommit();
	}
	public void setAutoCommit(boolean flag) throws SQLException{
		conn.setAutoCommit(flag);
	}
	public int execute(String sqlcmd, Object...args) throws SQLException{
		try{
			return updateStatus(conn.execute(sqlcmd, args), sqlcmd, args, null);
		}
		catch(SQLException e){
			updateStatus(sqlcmd, args, e);
			throw e;
		}
	}
	public int query(dbx.DBConnect.ResultLoop loop, String sqlcmd, Object...args) throws SQLException {
		try{
			return updateStatus(conn.query(loop, sqlcmd, args), sqlcmd, args, null);
		}
		catch(SQLException e){
			updateStatus(sqlcmd, args, e);
			throw e;
		}
	}
	public <T> T select(Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
		ArrayList<T> vec = selectList(clazz, sqlcmd, args);
		return vec.isEmpty() ? null : vec.get(0);
	}
	public <T> ArrayList<T> selectList(final Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
		try{
			ArrayList<T> res = conn.selectList(clazz, sqlcmd, args);
			updateStatus(res.size(), sqlcmd, args, null);
			return res;
		}
		catch(SQLException e){
			updateStatus(sqlcmd, args, e);
			throw e;
		}
	}
	public int insert(Object record, String tabname, String exclude) throws Exception, SQLException{
		String cols = "";
		String conds = "";
		List<Object> paramlist = new ArrayList<>();
		Field[] fields = record.getClass().getDeclaredFields();

		if (Utils.IsNotEmpty(exclude)) exclude = "," + exclude + ",";

		for (Field field : fields){
			String name = field.getName();
			String ename = "," + name + ",";

			if (Utils.IsNotEmpty(exclude) && exclude.indexOf(ename) >= 0) continue;

			cols += "," + name;
			conds += ",?";

			paramlist.add(field.get(record));
		}

		if (cols.isEmpty()) Utils.Throw(Utils.PARAMERR);

		String sql = String.format("insert into %s(%s) values(%s)", tabname, cols.substring(1), conds.substring(1));

		return execute(sql, paramlist.toArray());
	}
	public int update(Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		String cols = "";
		String conds = "1=1";
		List<Object> condlist = new ArrayList<>();
		List<Object> paramlist = new ArrayList<>();
		Field[] fields = record.getClass().getDeclaredFields();

		if (Utils.IsNotEmpty(keylist)) keylist = "," + keylist + ",";
		if (Utils.IsNotEmpty(exclude)) exclude = "," + exclude + ",";

		for (Field field : fields){
			String name = field.getName();
			String ename = "," + name + ",";

			if (Utils.IsNotEmpty(exclude) && exclude.indexOf(ename) >= 0) continue;

			if (Utils.IsNotEmpty(keylist) && keylist.indexOf(ename) >= 0){
				conds += " and " + name + "=?";
				condlist.add(field.get(record));
			}
			else{
				Object data = field.get(record);

				if (updatenull || data != null) {
					cols += "," + name + "=?";
					paramlist.add(data);
				}
			}
		}

		if (cols.isEmpty()) Utils.Throw(Utils.PARAMERR);

		paramlist.addAll(condlist);

		String sql = "update " + tabname + " set " + cols.substring(1) + " where " + conds;

		return execute(sql, paramlist.toArray());
	}
	public int replace(Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		int res = update(record, tabname, keylist, exclude, updatenull);

		if (res == 0) res = insert(record, tabname, exclude);

		return res;
	}

	public static DBConnect Connect() throws Exception{
		return new DBConnect(dbx.DBConnect.Connect());
	}
	public static DBConnect Connect(String poolname) throws Exception{
		return new DBConnect(dbx.DBConnect.Connect(poolname));
	}
	public static int Query(dbx.DBConnect.ResultLoop loop, String sqlcmd, Object...args) throws Exception, SQLException {
		return Query(null, loop, sqlcmd, args);
	}
	public static int Query(String poolname, dbx.DBConnect.ResultLoop loop, String sqlcmd, Object...args) throws Exception, SQLException {
		DBConnect conn = Connect(poolname);

		try{
			return conn.query(loop, sqlcmd, args);
		}
		finally{
			conn.close();
		}
	}
	public static <T> T Select(Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		return Select(null, clazz, sqlcmd, args);
	}
	public static <T> T Select(String poolname, Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		ArrayList<T> vec = SelectList(poolname, clazz, sqlcmd, args);
		return vec.isEmpty() ? null : vec.get(0);
	}
	public static <T> ArrayList<T> SelectList(final Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		return SelectList(null, clazz, sqlcmd, args);
	}
	public static <T> ArrayList<T> SelectList(String poolname, final Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.selectList(clazz, sqlcmd, args);
		}
		finally{
			conn.close();
		}
	}
	public static int Insert(String poolname, Object record, String tabname, String exclude) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.insert(record, tabname, exclude);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Update(String poolname, Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.update(record, tabname, keylist, exclude, updatenull);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Replace(String poolname, Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.replace(record, tabname, keylist, exclude, updatenull);
		}
		finally{
			Utils.Close(conn);
		}
	}
}